# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import csv
import mysql.connector
from mysql.connector import Error
from itemadapter import ItemAdapter


class WyjobPipeline:
    def open_spider(self,spider):
        self.fp = open('职位.csv', 'w', encoding='utf-8-sig', newline='')
        self.writer = csv.writer(self.fp)
        self.writer.writerow(['职位名称', '职位所属', '学历', '经验', '描述', '要求', '人数', '地点', '时间', '详情页'])

    def process_item(self, item, spider):
        row = [item['name'],item['productName'],item['reqEducation'],item['reqWorkYears'],item['description'],item['requirement'],item['recruitNum'],item['workPlaceName'],item['updateTime'],'https://hr.163.com/job-detail.html?id={}'.format(item['id'])]
        self.writer.writerow(row)
        return item

    def close_spider(self, spider):
        self.fp.close()

class MySQLPipeline:
    def __init__(self):
        self.connection = None
        self.cursor = None

    def open_spider(self, spider):
        try:
            self.connection = mysql.connector.connect(
                host='localhost',
                user='root',
                password='123456',
                database='wyjob',
                charset='utf8mb4'
            )
            self.cursor = self.connection.cursor()
        except Error as e:
            print(f"Error while connecting to MySQL:{e}")
    def close_spider(self, spider):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
    def process_item(self, item, spider):
        try:
            insert_query = """
            insert into jobinfo (name,productName,reqEducation,reqWorkYears,description,requirement,recruitNum,workPlaceName,updateTime,job_link) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            """
            val = (
                item['name'],
                item['productName'],
                item['reqEducation'],
                item['reqWorkYears'],
                item['description'],
                item['requirement'],
                item['recruitNum'],
                item['workPlaceName'],
                item['updateTime'],
                'https://hr.163.com/job-detail.html?id={}'.format(item['id'])
            )
            self.cursor.execute(insert_query, val)
            self.connection.commit()
        except Error as e:
            print(e)